﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ModelLayer;

namespace CinemaDbLib
{
	public class UserDB
	{
		private Connection connection;

		public UserDB()
		{
			connection = new Connection();
		}

		public List<User> getUsers()
		{
			return where("");
		}

		public User getUserById(int id)
		{
			return singleWhere("id = " + id);
		}

        public User getUserByUsername(string username)
        {
            return singleWhere("email='" + username + "'");
        }

		private User singleWhere(String wClause)
		{
			User user = null;

			string query = "SELECT * FROM Users WHERE " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);
			if (reader.Read())
			{
				user = new User();
				user.id = reader.GetInt32(0);
				user.email = reader.GetString(1);
				user.password = reader.GetString(2);
				user.name = reader.GetString(3);
			}

			reader.Close();
			connection.Close();

			return user;
		}

		private List<User> where(String wClause)
		{
			List<User> users = new List<User>();

			string query = "SELECT * FROM Users " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			while (reader.Read())
			{
				User user = new User();
				user.id = reader.GetInt32(0);
				user.email = reader.GetString(1);
				user.password = reader.GetString(2);
				user.name = reader.GetString(3);
				users.Add(user);
			}

			reader.Close();
			connection.Close();

			return users;
		}

		public bool removeUser(int id)
		{
			string query = "DELETE FROM Users WHERE id=" + id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}

		public int insertUser(User user)
		{
			int id = connection.GetMaxId("Users") + 1;
			string query = @"INSERT INTO Users (id, email, password, name) "+
				"VALUES ( " + id + ", '" + user.email + "', '" + user.password 
				+ "', '" + user.name + "')";
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			
			if (result == 1)
				return id;
			return -1;
		}

		public bool updateUser(User user)
		{
			string query = @"UPDATE Users SET name='" + user.name + 
				"', email='" + user.email + "', password='" + user.password + 
				"' WHERE id=" + user.id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}
	}
}
